package com.linln.modules.gdp.repository;

import com.linln.modules.gdp.domain.RegionGdp;
import com.linln.modules.system.repository.BaseRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * @author cocia
 * @date 2021/10/10
 */
public interface RegionGdpRepository extends BaseRepository<RegionGdp, Long> {
    List<RegionGdp> findByName(String name);
    void deleteAllByName(String name);
    @Query(value = "SELECT name FROM from or_region_gdp GROUP BY name ORDER BY name DESC",nativeQuery=true)
    List<Map<String, String>> findAllNames();

    @Query(value = "SELECT count(*) as total,apply_item as applyItem FROM or_region_gdp where status='1' and apply_date between ? and ? GROUP BY apply_item ORDER BY  apply_item ASC",nativeQuery=true)
    List<Map<String, String>> statByApplyItem(String startTime,String endTime);

    @Query(value = "SELECT count(*) as total FROM or_region_gdp where status='1'",nativeQuery=true)
    int statApplyCount();

    @Query(value = "SELECT count(*) as total FROM or_region_gdp WHERE status='1' and review_status='是'",nativeQuery=true)
    int statApplyCountByTech();


    @Query(value = "SELECT SUM(check_nums) as total FROM or_region_gdp where status='1'",nativeQuery=true)
    int statTotalAuditCount();

    @Query(value = "SELECT count(*) as total FROM or_region_gdp  WHERE check_status='未审核' and status='1'",nativeQuery=true)
    int statUnAuditCount();



    @Query(value = "SELECT count(*) as total,check_name as checkName FROM or_region_gdp where status='1'and apply_date between ? and ? GROUP BY check_name ORDER BY  apply_item ASC",nativeQuery=true)
    List<Map<String, String>> statByCheckName(String startTime,String endTime);



    @Query(value = "SELECT year(apply_date) as year,MONTH(apply_date) as month,COUNT(*) as total,apply_item as applyItem from or_region_gdp where status='1'and  year(apply_date)=? GROUP BY year(apply_date),MONTH(apply_date),apply_item",nativeQuery=true)
    List<Map<String, String>> statApplyItemByYear(String year);

    @Query(value = "SELECT year(apply_date) as year,MONTH(apply_date) as month,COUNT(*) as total,apply_item as applyItem from or_region_gdp where  year(apply_date)=? GROUP BY year(apply_date),MONTH(apply_date),apply_item",nativeQuery=true)
    List<Map<String, String>> statCheckNumsByYear(String year);

    @Query(value = "SELECT year(apply_date) as year,MONTH(apply_date) as month,COUNT(*) as total,check_name as checkName from or_region_gdp where  year(apply_date)=? GROUP BY year(apply_date),MONTH(apply_date),check_name",nativeQuery=true)
    List<Map<String, String>> statCheckNumsAndNameByYear(String year);
}